{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ee114857",
   "metadata": {},
   "source": [
    "## Manipulating and transforming DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8dec731c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "32d193b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from IPython.display import display\n",
    "from openbb import obb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0b109144",
   "metadata": {},
   "outputs": [],
   "source": [
    "warnings.filterwarnings(\"ignore\")\n",
    "obb.user.preferences.output_type = \"dataframe\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5ba3959",
   "metadata": {},
   "source": [
    "Fetches historical price data for the equity \"AAPL\" using the \"yfinance\" provider and stores it in 'asset'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "029b99c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset = obb.equity.price.historical(\"AAPL\", provider=\"yfinance\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7a9f4ec2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>split_ratio</th>\n",
       "      <th>dividend</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2023-06-20</th>\n",
       "      <td>184.410004</td>\n",
       "      <td>186.100006</td>\n",
       "      <td>184.410004</td>\n",
       "      <td>185.009995</td>\n",
       "      <td>49799100</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-21</th>\n",
       "      <td>184.899994</td>\n",
       "      <td>185.410004</td>\n",
       "      <td>182.589996</td>\n",
       "      <td>183.960007</td>\n",
       "      <td>49515700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-22</th>\n",
       "      <td>183.740005</td>\n",
       "      <td>187.050003</td>\n",
       "      <td>183.669998</td>\n",
       "      <td>187.000000</td>\n",
       "      <td>51245300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-23</th>\n",
       "      <td>185.550003</td>\n",
       "      <td>187.559998</td>\n",
       "      <td>185.009995</td>\n",
       "      <td>186.679993</td>\n",
       "      <td>53079300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-26</th>\n",
       "      <td>186.830002</td>\n",
       "      <td>188.050003</td>\n",
       "      <td>185.229996</td>\n",
       "      <td>185.270004</td>\n",
       "      <td>48088700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-06-12</th>\n",
       "      <td>207.369995</td>\n",
       "      <td>220.199997</td>\n",
       "      <td>206.899994</td>\n",
       "      <td>213.070007</td>\n",
       "      <td>198134300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-06-13</th>\n",
       "      <td>214.740005</td>\n",
       "      <td>216.750000</td>\n",
       "      <td>211.600006</td>\n",
       "      <td>214.240005</td>\n",
       "      <td>97862700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-06-14</th>\n",
       "      <td>213.850006</td>\n",
       "      <td>215.169998</td>\n",
       "      <td>211.300003</td>\n",
       "      <td>212.490005</td>\n",
       "      <td>70122700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-06-17</th>\n",
       "      <td>213.369995</td>\n",
       "      <td>218.949997</td>\n",
       "      <td>212.720001</td>\n",
       "      <td>216.669998</td>\n",
       "      <td>93728300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-06-18</th>\n",
       "      <td>217.589996</td>\n",
       "      <td>218.630005</td>\n",
       "      <td>213.000000</td>\n",
       "      <td>214.289993</td>\n",
       "      <td>79854200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>252 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  open        high         low       close     volume  \\\n",
       "date                                                                    \n",
       "2023-06-20  184.410004  186.100006  184.410004  185.009995   49799100   \n",
       "2023-06-21  184.899994  185.410004  182.589996  183.960007   49515700   \n",
       "2023-06-22  183.740005  187.050003  183.669998  187.000000   51245300   \n",
       "2023-06-23  185.550003  187.559998  185.009995  186.679993   53079300   \n",
       "2023-06-26  186.830002  188.050003  185.229996  185.270004   48088700   \n",
       "...                ...         ...         ...         ...        ...   \n",
       "2024-06-12  207.369995  220.199997  206.899994  213.070007  198134300   \n",
       "2024-06-13  214.740005  216.750000  211.600006  214.240005   97862700   \n",
       "2024-06-14  213.850006  215.169998  211.300003  212.490005   70122700   \n",
       "2024-06-17  213.369995  218.949997  212.720001  216.669998   93728300   \n",
       "2024-06-18  217.589996  218.630005  213.000000  214.289993   79854200   \n",
       "\n",
       "            split_ratio  dividend  \n",
       "date                               \n",
       "2023-06-20          0.0       0.0  \n",
       "2023-06-21          0.0       0.0  \n",
       "2023-06-22          0.0       0.0  \n",
       "2023-06-23          0.0       0.0  \n",
       "2023-06-26          0.0       0.0  \n",
       "...                 ...       ...  \n",
       "2024-06-12          0.0       0.0  \n",
       "2024-06-13          0.0       0.0  \n",
       "2024-06-14          0.0       0.0  \n",
       "2024-06-17          0.0       0.0  \n",
       "2024-06-18          0.0       0.0  \n",
       "\n",
       "[252 rows x 7 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(asset)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e265e975",
   "metadata": {},
   "source": [
    "Fetches historical price data for the equity \"SPY\" using the \"yfinance\" provider and stores it in 'benchmark'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6faf63bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "benchmark = obb.equity.price.historical(\"SPY\", provider=\"yfinance\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9d19bf66",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>split_ratio</th>\n",
       "      <th>dividend</th>\n",
       "      <th>capital_gains</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2023-06-20</th>\n",
       "      <td>437.450012</td>\n",
       "      <td>438.369995</td>\n",
       "      <td>435.029999</td>\n",
       "      <td>437.179993</td>\n",
       "      <td>76160400</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-21</th>\n",
       "      <td>436.160004</td>\n",
       "      <td>436.989990</td>\n",
       "      <td>434.329987</td>\n",
       "      <td>434.940002</td>\n",
       "      <td>76982300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-22</th>\n",
       "      <td>433.950012</td>\n",
       "      <td>436.619995</td>\n",
       "      <td>433.600006</td>\n",
       "      <td>436.510010</td>\n",
       "      <td>70637200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-23</th>\n",
       "      <td>432.929993</td>\n",
       "      <td>435.059998</td>\n",
       "      <td>432.470001</td>\n",
       "      <td>433.209991</td>\n",
       "      <td>92074500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-26</th>\n",
       "      <td>432.619995</td>\n",
       "      <td>434.609985</td>\n",
       "      <td>431.190002</td>\n",
       "      <td>431.440002</td>\n",
       "      <td>72823600</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-06-12</th>\n",
       "      <td>541.630005</td>\n",
       "      <td>544.119995</td>\n",
       "      <td>540.299988</td>\n",
       "      <td>541.359985</td>\n",
       "      <td>63251300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-06-13</th>\n",
       "      <td>543.150024</td>\n",
       "      <td>543.330017</td>\n",
       "      <td>539.590027</td>\n",
       "      <td>542.450012</td>\n",
       "      <td>44760900</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-06-14</th>\n",
       "      <td>540.880005</td>\n",
       "      <td>542.809998</td>\n",
       "      <td>539.849976</td>\n",
       "      <td>542.780029</td>\n",
       "      <td>40089900</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-06-17</th>\n",
       "      <td>542.080017</td>\n",
       "      <td>548.530029</td>\n",
       "      <td>541.609985</td>\n",
       "      <td>547.099976</td>\n",
       "      <td>55839500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-06-18</th>\n",
       "      <td>547.159973</td>\n",
       "      <td>548.619995</td>\n",
       "      <td>546.729980</td>\n",
       "      <td>548.489990</td>\n",
       "      <td>41342200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>252 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  open        high         low       close    volume  \\\n",
       "date                                                                   \n",
       "2023-06-20  437.450012  438.369995  435.029999  437.179993  76160400   \n",
       "2023-06-21  436.160004  436.989990  434.329987  434.940002  76982300   \n",
       "2023-06-22  433.950012  436.619995  433.600006  436.510010  70637200   \n",
       "2023-06-23  432.929993  435.059998  432.470001  433.209991  92074500   \n",
       "2023-06-26  432.619995  434.609985  431.190002  431.440002  72823600   \n",
       "...                ...         ...         ...         ...       ...   \n",
       "2024-06-12  541.630005  544.119995  540.299988  541.359985  63251300   \n",
       "2024-06-13  543.150024  543.330017  539.590027  542.450012  44760900   \n",
       "2024-06-14  540.880005  542.809998  539.849976  542.780029  40089900   \n",
       "2024-06-17  542.080017  548.530029  541.609985  547.099976  55839500   \n",
       "2024-06-18  547.159973  548.619995  546.729980  548.489990  41342200   \n",
       "\n",
       "            split_ratio  dividend  capital_gains  \n",
       "date                                              \n",
       "2023-06-20          0.0       0.0            0.0  \n",
       "2023-06-21          0.0       0.0            0.0  \n",
       "2023-06-22          0.0       0.0            0.0  \n",
       "2023-06-23          0.0       0.0            0.0  \n",
       "2023-06-26          0.0       0.0            0.0  \n",
       "...                 ...       ...            ...  \n",
       "2024-06-12          0.0       0.0            0.0  \n",
       "2024-06-13          0.0       0.0            0.0  \n",
       "2024-06-14          0.0       0.0            0.0  \n",
       "2024-06-17          0.0       0.0            0.0  \n",
       "2024-06-18          0.0       0.0            0.0  \n",
       "\n",
       "[252 rows x 8 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(benchmark)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32901a41",
   "metadata": {},
   "source": [
    "Updates the column names for 'asset' and 'benchmark'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "677f3a2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = [\n",
    "    \"open\",\n",
    "    \"high\",\n",
    "    \"low\",\n",
    "    \"close\",\n",
    "    \"volume\",\n",
    "    \"dividends\",\n",
    "    \"splits\",\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a2b44bbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset.columns = columns\n",
    "benchmark.columns = columns + [\"capital_gain\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "928ca43c",
   "metadata": {},
   "source": [
    "Adds a new column 'price_diff' that contains the difference in closing prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b31474db",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset[\"price_diff\"] = asset.close.diff()\n",
    "benchmark[\"price_diff\"] = benchmark.close.diff()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4ff5c4c",
   "metadata": {},
   "source": [
    "Adds a new column 'gain' that indicates whether the price difference is positive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c602b069",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset[\"gain\"] = asset.price_diff > 0\n",
    "benchmark[\"gain\"] = benchmark.price_diff > 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83b2f582",
   "metadata": {},
   "source": [
    "Adds a new column 'symbol' with the equity symbol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ca720866",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset[\"symbol\"] = \"AAPL\"\n",
    "benchmark[\"symbol\"] = \"SPY\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f304b6d",
   "metadata": {},
   "source": [
    "Sets the volume of the 10th row to the mean volume of rows 5 to 9 in 'asset_2'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "71e51ec8",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset_2 = asset.copy()\n",
    "asset_2.at[asset_2.index[10], \"volume\"] = asset_2.volume[asset_2.index[5:10]].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "448d8e4c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "asset_2.iat[10, 5]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "467210a0",
   "metadata": {},
   "source": [
    "Concatenates 'asset' and 'asset_2' and drops duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d74bd576",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([asset, asset_2]).drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce74bcfa",
   "metadata": {},
   "source": [
    "Adds a new column 'returns' that contains the percentage change in closing prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f293cf69",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset[\"returns\"] = asset[\"close\"].pct_change()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6473e04",
   "metadata": {},
   "source": [
    "Creates a pivot table from 'asset' with 'returns' as values, 'gain' as columns, and 'sum', 'mean', 'std' as aggregation functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "411486cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.pivot_table(\n",
    "    data=asset, values=\"returns\", columns=\"gain\", aggfunc=[\"sum\", \"mean\", \"std\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4812cbaf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"2\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"2\" halign=\"left\">std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gain</th>\n",
       "      <th>False</th>\n",
       "      <th>True</th>\n",
       "      <th>False</th>\n",
       "      <th>True</th>\n",
       "      <th>False</th>\n",
       "      <th>True</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>returns</th>\n",
       "      <td>-1.173091</td>\n",
       "      <td>1.34348</td>\n",
       "      <td>-0.009858</td>\n",
       "      <td>0.010178</td>\n",
       "      <td>0.008515</td>\n",
       "      <td>0.010122</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              sum               mean                 std          \n",
       "gain        False    True      False     True      False     True \n",
       "returns -1.173091  1.34348 -0.009858  0.010178  0.008515  0.010122"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd52e0e2",
   "metadata": {},
   "source": [
    "Concatenates 'asset' and 'benchmark' and groups by 'symbol' to calculate open, high, low, close (OHLC) prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "4fc4b8d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "concated = pd.concat([asset, benchmark])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "bb835388",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = concated.groupby(\"symbol\").close.ohlc()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "86b78ceb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>symbol</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AAPL</th>\n",
       "      <td>185.009995</td>\n",
       "      <td>216.669998</td>\n",
       "      <td>165.000000</td>\n",
       "      <td>214.289993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SPY</th>\n",
       "      <td>437.179993</td>\n",
       "      <td>548.489990</td>\n",
       "      <td>410.679993</td>\n",
       "      <td>548.489990</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              open        high         low       close\n",
       "symbol                                                \n",
       "AAPL    185.009995  216.669998  165.000000  214.289993\n",
       "SPY     437.179993  548.489990  410.679993  548.489990"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea41cecf",
   "metadata": {},
   "source": [
    "Fetches options chains for the symbol \"AAPL\" using the \"cboe\" provider and stores it in 'chains'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "89b44574",
   "metadata": {},
   "outputs": [],
   "source": [
    "chains = obb.derivatives.options.chains(\"AAPL\", provider=\"cboe\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d4eb1095",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contract_symbol</th>\n",
       "      <th>expiration</th>\n",
       "      <th>strike</th>\n",
       "      <th>option_type</th>\n",
       "      <th>open_interest</th>\n",
       "      <th>volume</th>\n",
       "      <th>theoretical_price</th>\n",
       "      <th>last_trade_price</th>\n",
       "      <th>tick</th>\n",
       "      <th>bid</th>\n",
       "      <th>...</th>\n",
       "      <th>change</th>\n",
       "      <th>change_percent</th>\n",
       "      <th>implied_volatility</th>\n",
       "      <th>delta</th>\n",
       "      <th>gamma</th>\n",
       "      <th>theta</th>\n",
       "      <th>vega</th>\n",
       "      <th>rho</th>\n",
       "      <th>last_trade_timestamp</th>\n",
       "      <th>dte</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPL240621C00005000</td>\n",
       "      <td>2024-06-21</td>\n",
       "      <td>5.0</td>\n",
       "      <td>call</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>209.2267</td>\n",
       "      <td>186.82</td>\n",
       "      <td>up</td>\n",
       "      <td>208.30</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0006</td>\n",
       "      <td>2024-05-30 15:17:02</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL240621P00005000</td>\n",
       "      <td>2024-06-21</td>\n",
       "      <td>5.0</td>\n",
       "      <td>put</td>\n",
       "      <td>35</td>\n",
       "      <td>6</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.01</td>\n",
       "      <td>no_change</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.005</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2024-06-18 13:34:12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAPL240621C00010000</td>\n",
       "      <td>2024-06-21</td>\n",
       "      <td>10.0</td>\n",
       "      <td>call</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>204.2299</td>\n",
       "      <td>171.61</td>\n",
       "      <td>down</td>\n",
       "      <td>203.40</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0010</td>\n",
       "      <td>2024-02-26 15:49:52</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL240621P00010000</td>\n",
       "      <td>2024-06-21</td>\n",
       "      <td>10.0</td>\n",
       "      <td>put</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.01</td>\n",
       "      <td>up</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2024-06-14 10:14:16</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPL240621C00015000</td>\n",
       "      <td>2024-06-21</td>\n",
       "      <td>15.0</td>\n",
       "      <td>call</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>199.2330</td>\n",
       "      <td>156.73</td>\n",
       "      <td>down</td>\n",
       "      <td>198.50</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0017</td>\n",
       "      <td>2024-03-28 09:31:59</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2121</th>\n",
       "      <td>AAPL261218P00330000</td>\n",
       "      <td>2026-12-18</td>\n",
       "      <td>330.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>115.7950</td>\n",
       "      <td>134.83</td>\n",
       "      <td>no_change</td>\n",
       "      <td>113.50</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.2884</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0285</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2024-06-06 15:39:43</td>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2122</th>\n",
       "      <td>AAPL261218C00340000</td>\n",
       "      <td>2026-12-18</td>\n",
       "      <td>340.0</td>\n",
       "      <td>call</td>\n",
       "      <td>174</td>\n",
       "      <td>21</td>\n",
       "      <td>8.2353</td>\n",
       "      <td>8.20</td>\n",
       "      <td>up</td>\n",
       "      <td>6.95</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.350</td>\n",
       "      <td>-0.0409</td>\n",
       "      <td>0.2340</td>\n",
       "      <td>0.2210</td>\n",
       "      <td>0.0038</td>\n",
       "      <td>-0.0113</td>\n",
       "      <td>1.0073</td>\n",
       "      <td>0.9640</td>\n",
       "      <td>2024-06-18 14:41:03</td>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2123</th>\n",
       "      <td>AAPL261218P00340000</td>\n",
       "      <td>2026-12-18</td>\n",
       "      <td>340.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>125.7950</td>\n",
       "      <td>126.45</td>\n",
       "      <td>up</td>\n",
       "      <td>123.55</td>\n",
       "      <td>...</td>\n",
       "      <td>3.775</td>\n",
       "      <td>0.0308</td>\n",
       "      <td>0.3039</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0285</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2024-06-18 11:18:51</td>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2124</th>\n",
       "      <td>AAPL261218C00350000</td>\n",
       "      <td>2026-12-18</td>\n",
       "      <td>350.0</td>\n",
       "      <td>call</td>\n",
       "      <td>2604</td>\n",
       "      <td>128</td>\n",
       "      <td>7.2132</td>\n",
       "      <td>7.40</td>\n",
       "      <td>no_change</td>\n",
       "      <td>7.10</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.025</td>\n",
       "      <td>-0.0034</td>\n",
       "      <td>0.2348</td>\n",
       "      <td>0.1989</td>\n",
       "      <td>0.0036</td>\n",
       "      <td>-0.0107</td>\n",
       "      <td>0.9493</td>\n",
       "      <td>0.8726</td>\n",
       "      <td>2024-06-18 15:33:39</td>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2125</th>\n",
       "      <td>AAPL261218P00350000</td>\n",
       "      <td>2026-12-18</td>\n",
       "      <td>350.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>135.7950</td>\n",
       "      <td>134.40</td>\n",
       "      <td>down</td>\n",
       "      <td>133.50</td>\n",
       "      <td>...</td>\n",
       "      <td>1.725</td>\n",
       "      <td>0.0130</td>\n",
       "      <td>0.3190</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0285</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2024-06-18 11:20:26</td>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2126 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          contract_symbol  expiration  strike option_type  open_interest  \\\n",
       "0     AAPL240621C00005000  2024-06-21     5.0        call             70   \n",
       "1     AAPL240621P00005000  2024-06-21     5.0         put             35   \n",
       "2     AAPL240621C00010000  2024-06-21    10.0        call              0   \n",
       "3     AAPL240621P00010000  2024-06-21    10.0         put              1   \n",
       "4     AAPL240621C00015000  2024-06-21    15.0        call              0   \n",
       "...                   ...         ...     ...         ...            ...   \n",
       "2121  AAPL261218P00330000  2026-12-18   330.0         put              0   \n",
       "2122  AAPL261218C00340000  2026-12-18   340.0        call            174   \n",
       "2123  AAPL261218P00340000  2026-12-18   340.0         put              0   \n",
       "2124  AAPL261218C00350000  2026-12-18   350.0        call           2604   \n",
       "2125  AAPL261218P00350000  2026-12-18   350.0         put              0   \n",
       "\n",
       "      volume  theoretical_price  last_trade_price       tick     bid  ...  \\\n",
       "0          0           209.2267            186.82         up  208.30  ...   \n",
       "1          6             0.0000              0.01  no_change    0.00  ...   \n",
       "2          0           204.2299            171.61       down  203.40  ...   \n",
       "3          0             0.0000              0.01         up    0.00  ...   \n",
       "4          0           199.2330            156.73       down  198.50  ...   \n",
       "...      ...                ...               ...        ...     ...  ...   \n",
       "2121       0           115.7950            134.83  no_change  113.50  ...   \n",
       "2122      21             8.2353              8.20         up    6.95  ...   \n",
       "2123      14           125.7950            126.45         up  123.55  ...   \n",
       "2124     128             7.2132              7.40  no_change    7.10  ...   \n",
       "2125       7           135.7950            134.40       down  133.50  ...   \n",
       "\n",
       "      change  change_percent  implied_volatility   delta   gamma   theta  \\\n",
       "0      0.000          0.0000              0.0000  1.0000  0.0000  0.0000   \n",
       "1      0.005          1.0000              0.0000  0.0000  0.0000  0.0000   \n",
       "2      0.000          0.0000              0.0000  1.0000  0.0000  0.0000   \n",
       "3      0.000          0.0000              0.0000  0.0000  0.0000  0.0000   \n",
       "4      0.000          0.0000              0.0000  1.0000  0.0000  0.0000   \n",
       "...      ...             ...                 ...     ...     ...     ...   \n",
       "2121   0.000          0.0000              0.2884 -1.0000  0.0000 -0.0285   \n",
       "2122  -0.350         -0.0409              0.2340  0.2210  0.0038 -0.0113   \n",
       "2123   3.775          0.0308              0.3039 -1.0000  0.0000 -0.0285   \n",
       "2124  -0.025         -0.0034              0.2348  0.1989  0.0036 -0.0107   \n",
       "2125   1.725          0.0130              0.3190 -1.0000  0.0000 -0.0285   \n",
       "\n",
       "        vega     rho  last_trade_timestamp  dte  \n",
       "0     0.0000  0.0006   2024-05-30 15:17:02    2  \n",
       "1     0.0000  0.0000   2024-06-18 13:34:12    2  \n",
       "2     0.0000  0.0010   2024-02-26 15:49:52    2  \n",
       "3     0.0000  0.0000   2024-06-14 10:14:16    2  \n",
       "4     0.0000  0.0017   2024-03-28 09:31:59    2  \n",
       "...      ...     ...                   ...  ...  \n",
       "2121  0.0000  0.0000   2024-06-06 15:39:43  912  \n",
       "2122  1.0073  0.9640   2024-06-18 14:41:03  912  \n",
       "2123  0.0000  0.0000   2024-06-18 11:18:51  912  \n",
       "2124  0.9493  0.8726   2024-06-18 15:33:39  912  \n",
       "2125  0.0000  0.0000   2024-06-18 11:20:26  912  \n",
       "\n",
       "[2126 rows x 27 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(chains)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a1e8cf5",
   "metadata": {},
   "source": [
    "Extracts unique expiration dates from 'chains'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c04cfb6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "expirations = chains.expiration.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "959e2556",
   "metadata": {},
   "source": [
    "Filters 'chains' to get call options expiring on the 6th expiration date and stores it in 'calls'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "92f9ede1",
   "metadata": {},
   "outputs": [],
   "source": [
    "calls = chains[(chains.option_type == \"call\") & (chains.expiration == expirations[5])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21ef4ceb",
   "metadata": {},
   "source": [
    "Filters 'chains' to get put options expiring on the 6th expiration date and stores it in 'puts'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "76e2bf01",
   "metadata": {},
   "outputs": [],
   "source": [
    "puts = chains[(chains.option_type == \"put\") & (chains.expiration == expirations[5])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b75b5d41",
   "metadata": {},
   "source": [
    "Sets the index of 'calls' and 'puts' to 'strike'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ec7d29a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "calls_strike = calls.set_index(\"strike\")\n",
    "puts_strike = puts.set_index(\"strike\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "784ed9f6",
   "metadata": {},
   "source": [
    "Joins 'calls_strike' and 'puts_strike' on the 'strike' index with a left join, adding suffixes to overlapping column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "995dafc7",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined = calls_strike.join(puts_strike, how=\"left\", lsuffix=\"_call\", rsuffix=\"_put\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbc36c7d",
   "metadata": {},
   "source": [
    "Extracts the last trade prices of call and put options and stores them in 'prices'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "999c4e35",
   "metadata": {},
   "outputs": [],
   "source": [
    "prices = joined[[\"last_trade_price_call\", \"last_trade_price_put\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e026a361",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>last_trade_price_call</th>\n",
       "      <th>last_trade_price_put</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strike</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100.0</th>\n",
       "      <td>118.76</td>\n",
       "      <td>0.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130.0</th>\n",
       "      <td>82.34</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140.0</th>\n",
       "      <td>78.83</td>\n",
       "      <td>0.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150.0</th>\n",
       "      <td>65.20</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155.0</th>\n",
       "      <td>60.25</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160.0</th>\n",
       "      <td>55.70</td>\n",
       "      <td>0.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165.0</th>\n",
       "      <td>51.55</td>\n",
       "      <td>0.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170.0</th>\n",
       "      <td>47.20</td>\n",
       "      <td>0.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175.0</th>\n",
       "      <td>39.83</td>\n",
       "      <td>0.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180.0</th>\n",
       "      <td>35.27</td>\n",
       "      <td>0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185.0</th>\n",
       "      <td>30.60</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190.0</th>\n",
       "      <td>25.53</td>\n",
       "      <td>0.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195.0</th>\n",
       "      <td>21.11</td>\n",
       "      <td>0.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200.0</th>\n",
       "      <td>16.68</td>\n",
       "      <td>1.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205.0</th>\n",
       "      <td>13.00</td>\n",
       "      <td>2.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210.0</th>\n",
       "      <td>9.00</td>\n",
       "      <td>3.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215.0</th>\n",
       "      <td>6.25</td>\n",
       "      <td>5.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220.0</th>\n",
       "      <td>4.10</td>\n",
       "      <td>8.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225.0</th>\n",
       "      <td>2.59</td>\n",
       "      <td>12.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230.0</th>\n",
       "      <td>1.67</td>\n",
       "      <td>15.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235.0</th>\n",
       "      <td>1.05</td>\n",
       "      <td>20.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240.0</th>\n",
       "      <td>0.71</td>\n",
       "      <td>24.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245.0</th>\n",
       "      <td>0.53</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250.0</th>\n",
       "      <td>0.40</td>\n",
       "      <td>36.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255.0</th>\n",
       "      <td>0.30</td>\n",
       "      <td>39.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260.0</th>\n",
       "      <td>0.26</td>\n",
       "      <td>44.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265.0</th>\n",
       "      <td>0.28</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270.0</th>\n",
       "      <td>0.18</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>275.0</th>\n",
       "      <td>0.18</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280.0</th>\n",
       "      <td>0.17</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285.0</th>\n",
       "      <td>0.10</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        last_trade_price_call  last_trade_price_put\n",
       "strike                                             \n",
       "100.0                  118.76                  0.06\n",
       "105.0                    0.00                  0.00\n",
       "110.0                    0.00                  0.06\n",
       "115.0                    0.00                  0.00\n",
       "120.0                    0.00                  0.00\n",
       "125.0                    0.00                  0.00\n",
       "130.0                   82.34                  0.00\n",
       "135.0                    0.00                  0.03\n",
       "140.0                   78.83                  0.21\n",
       "145.0                    0.00                  0.05\n",
       "150.0                   65.20                  0.08\n",
       "155.0                   60.25                  0.05\n",
       "160.0                   55.70                  0.09\n",
       "165.0                   51.55                  0.09\n",
       "170.0                   47.20                  0.09\n",
       "175.0                   39.83                  0.16\n",
       "180.0                   35.27                  0.23\n",
       "185.0                   30.60                  0.30\n",
       "190.0                   25.53                  0.46\n",
       "195.0                   21.11                  0.78\n",
       "200.0                   16.68                  1.31\n",
       "205.0                   13.00                  2.26\n",
       "210.0                    9.00                  3.75\n",
       "215.0                    6.25                  5.89\n",
       "220.0                    4.10                  8.35\n",
       "225.0                    2.59                 12.00\n",
       "230.0                    1.67                 15.50\n",
       "235.0                    1.05                 20.35\n",
       "240.0                    0.71                 24.29\n",
       "245.0                    0.53                  0.00\n",
       "250.0                    0.40                 36.50\n",
       "255.0                    0.30                 39.05\n",
       "260.0                    0.26                 44.75\n",
       "265.0                    0.28                  0.00\n",
       "270.0                    0.18                  0.00\n",
       "275.0                    0.18                  0.00\n",
       "280.0                    0.17                  0.00\n",
       "285.0                    0.10                  0.00"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(prices)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5359808a",
   "metadata": {},
   "source": [
    "Adds a new column 'straddle_price' that contains the sum of last trade prices of call and put options"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "b5a8b07b",
   "metadata": {},
   "outputs": [],
   "source": [
    "prices.loc[:, \"straddle_price\"] = prices.sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "fbd20a59",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>last_trade_price_call</th>\n",
       "      <th>last_trade_price_put</th>\n",
       "      <th>straddle_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strike</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100.0</th>\n",
       "      <td>118.76</td>\n",
       "      <td>0.06</td>\n",
       "      <td>118.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130.0</th>\n",
       "      <td>82.34</td>\n",
       "      <td>0.00</td>\n",
       "      <td>82.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140.0</th>\n",
       "      <td>78.83</td>\n",
       "      <td>0.21</td>\n",
       "      <td>79.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150.0</th>\n",
       "      <td>65.20</td>\n",
       "      <td>0.08</td>\n",
       "      <td>65.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155.0</th>\n",
       "      <td>60.25</td>\n",
       "      <td>0.05</td>\n",
       "      <td>60.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160.0</th>\n",
       "      <td>55.70</td>\n",
       "      <td>0.09</td>\n",
       "      <td>55.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165.0</th>\n",
       "      <td>51.55</td>\n",
       "      <td>0.09</td>\n",
       "      <td>51.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170.0</th>\n",
       "      <td>47.20</td>\n",
       "      <td>0.09</td>\n",
       "      <td>47.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175.0</th>\n",
       "      <td>39.83</td>\n",
       "      <td>0.16</td>\n",
       "      <td>39.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180.0</th>\n",
       "      <td>35.27</td>\n",
       "      <td>0.23</td>\n",
       "      <td>35.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185.0</th>\n",
       "      <td>30.60</td>\n",
       "      <td>0.30</td>\n",
       "      <td>30.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190.0</th>\n",
       "      <td>25.53</td>\n",
       "      <td>0.46</td>\n",
       "      <td>25.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195.0</th>\n",
       "      <td>21.11</td>\n",
       "      <td>0.78</td>\n",
       "      <td>21.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200.0</th>\n",
       "      <td>16.68</td>\n",
       "      <td>1.31</td>\n",
       "      <td>17.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205.0</th>\n",
       "      <td>13.00</td>\n",
       "      <td>2.26</td>\n",
       "      <td>15.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210.0</th>\n",
       "      <td>9.00</td>\n",
       "      <td>3.75</td>\n",
       "      <td>12.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215.0</th>\n",
       "      <td>6.25</td>\n",
       "      <td>5.89</td>\n",
       "      <td>12.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220.0</th>\n",
       "      <td>4.10</td>\n",
       "      <td>8.35</td>\n",
       "      <td>12.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225.0</th>\n",
       "      <td>2.59</td>\n",
       "      <td>12.00</td>\n",
       "      <td>14.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230.0</th>\n",
       "      <td>1.67</td>\n",
       "      <td>15.50</td>\n",
       "      <td>17.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235.0</th>\n",
       "      <td>1.05</td>\n",
       "      <td>20.35</td>\n",
       "      <td>21.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240.0</th>\n",
       "      <td>0.71</td>\n",
       "      <td>24.29</td>\n",
       "      <td>25.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245.0</th>\n",
       "      <td>0.53</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250.0</th>\n",
       "      <td>0.40</td>\n",
       "      <td>36.50</td>\n",
       "      <td>36.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255.0</th>\n",
       "      <td>0.30</td>\n",
       "      <td>39.05</td>\n",
       "      <td>39.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260.0</th>\n",
       "      <td>0.26</td>\n",
       "      <td>44.75</td>\n",
       "      <td>45.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265.0</th>\n",
       "      <td>0.28</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270.0</th>\n",
       "      <td>0.18</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>275.0</th>\n",
       "      <td>0.18</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280.0</th>\n",
       "      <td>0.17</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285.0</th>\n",
       "      <td>0.10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        last_trade_price_call  last_trade_price_put  straddle_price\n",
       "strike                                                             \n",
       "100.0                  118.76                  0.06          118.82\n",
       "105.0                    0.00                  0.00            0.00\n",
       "110.0                    0.00                  0.06            0.06\n",
       "115.0                    0.00                  0.00            0.00\n",
       "120.0                    0.00                  0.00            0.00\n",
       "125.0                    0.00                  0.00            0.00\n",
       "130.0                   82.34                  0.00           82.34\n",
       "135.0                    0.00                  0.03            0.03\n",
       "140.0                   78.83                  0.21           79.04\n",
       "145.0                    0.00                  0.05            0.05\n",
       "150.0                   65.20                  0.08           65.28\n",
       "155.0                   60.25                  0.05           60.30\n",
       "160.0                   55.70                  0.09           55.79\n",
       "165.0                   51.55                  0.09           51.64\n",
       "170.0                   47.20                  0.09           47.29\n",
       "175.0                   39.83                  0.16           39.99\n",
       "180.0                   35.27                  0.23           35.50\n",
       "185.0                   30.60                  0.30           30.90\n",
       "190.0                   25.53                  0.46           25.99\n",
       "195.0                   21.11                  0.78           21.89\n",
       "200.0                   16.68                  1.31           17.99\n",
       "205.0                   13.00                  2.26           15.26\n",
       "210.0                    9.00                  3.75           12.75\n",
       "215.0                    6.25                  5.89           12.14\n",
       "220.0                    4.10                  8.35           12.45\n",
       "225.0                    2.59                 12.00           14.59\n",
       "230.0                    1.67                 15.50           17.17\n",
       "235.0                    1.05                 20.35           21.40\n",
       "240.0                    0.71                 24.29           25.00\n",
       "245.0                    0.53                  0.00            0.53\n",
       "250.0                    0.40                 36.50           36.90\n",
       "255.0                    0.30                 39.05           39.35\n",
       "260.0                    0.26                 44.75           45.01\n",
       "265.0                    0.28                  0.00            0.28\n",
       "270.0                    0.18                  0.00            0.18\n",
       "275.0                    0.18                  0.00            0.18\n",
       "280.0                    0.17                  0.00            0.17\n",
       "285.0                    0.10                  0.00            0.10"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(prices)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dfeb441",
   "metadata": {},
   "source": [
    "Groups 'chains' by option type, strike, and expiration to calculate the sum of open interest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "80c9245d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = chains.groupby([\"option_type\", \"strike\", \"expiration\"]).open_interest.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d57cdaf1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "option_type  strike  expiration\n",
       "call         5.0     2024-06-21    70\n",
       "                     2024-07-19    14\n",
       "                     2024-08-16     1\n",
       "                     2024-09-20     4\n",
       "                     2024-10-18     0\n",
       "                                   ..\n",
       "put          370.0   2024-08-16     0\n",
       "                     2024-10-18     0\n",
       "                     2024-11-15     0\n",
       "                     2025-03-21     0\n",
       "             380.0   2025-03-21     0\n",
       "Name: open_interest, Length: 2126, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df6f0d37",
   "metadata": {},
   "source": [
    "Groups 'chains' by option type, strike, and expiration to calculate the max last trade price and the sum of open interest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "5d641858",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = chains.groupby([\"option_type\", \"strike\", \"expiration\"]).agg(\n",
    "    {\"last_trade_price\": \"max\", \"open_interest\": \"sum\"}\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "fc2d706e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>last_trade_price</th>\n",
       "      <th>open_interest</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>option_type</th>\n",
       "      <th>strike</th>\n",
       "      <th>expiration</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">call</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">5.0</th>\n",
       "      <th>2024-06-21</th>\n",
       "      <td>186.82</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-07-19</th>\n",
       "      <td>210.74</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-16</th>\n",
       "      <td>182.23</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-09-20</th>\n",
       "      <td>208.94</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-10-18</th>\n",
       "      <td>172.06</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">put</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">370.0</th>\n",
       "      <th>2024-08-16</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-10-18</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-11-15</th>\n",
       "      <td>187.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-03-21</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>380.0</th>\n",
       "      <th>2025-03-21</th>\n",
       "      <td>189.89</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2126 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               last_trade_price  open_interest\n",
       "option_type strike expiration                                 \n",
       "call        5.0    2024-06-21            186.82             70\n",
       "                   2024-07-19            210.74             14\n",
       "                   2024-08-16            182.23              1\n",
       "                   2024-09-20            208.94              4\n",
       "                   2024-10-18            172.06              0\n",
       "...                                         ...            ...\n",
       "put         370.0  2024-08-16              0.00              0\n",
       "                   2024-10-18              0.00              0\n",
       "                   2024-11-15            187.00              0\n",
       "                   2025-03-21              0.00              0\n",
       "            380.0  2025-03-21            189.89              0\n",
       "\n",
       "[2126 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7d7f691",
   "metadata": {},
   "source": [
    "Groups 'chains' by option type and calculates the mean bid-ask spread for each group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "9aa53222",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = chains.groupby([\"option_type\"]).apply(\n",
    "    lambda x: (x[\"ask\"] - x[\"bid\"]).mean()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "f2b475ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "option_type\n",
       "call    1.818636\n",
       "put     1.132643\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92c4c7e7",
   "metadata": {},
   "source": [
    "Standardizes the last trade prices by subtracting the mean and dividing by the standard deviation within each expiration group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "d146f7a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       2.965133\n",
       "1      -0.662307\n",
       "2       2.669788\n",
       "3      -0.662307\n",
       "4       2.380851\n",
       "          ...   \n",
       "2121    1.734031\n",
       "2122   -0.840912\n",
       "2123    1.563629\n",
       "2124   -0.857180\n",
       "2125    1.725288\n",
       "Name: last_trade_price, Length: 2126, dtype: float64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    chains.groupby(\"expiration\").last_trade_price.transform(\n",
    "        lambda x: (x - x.mean()) / x.std()\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "cbe17a7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contract_symbol</th>\n",
       "      <th>expiration</th>\n",
       "      <th>strike</th>\n",
       "      <th>option_type</th>\n",
       "      <th>open_interest</th>\n",
       "      <th>volume</th>\n",
       "      <th>theoretical_price</th>\n",
       "      <th>last_trade_price</th>\n",
       "      <th>tick</th>\n",
       "      <th>bid</th>\n",
       "      <th>...</th>\n",
       "      <th>change</th>\n",
       "      <th>change_percent</th>\n",
       "      <th>implied_volatility</th>\n",
       "      <th>delta</th>\n",
       "      <th>gamma</th>\n",
       "      <th>theta</th>\n",
       "      <th>vega</th>\n",
       "      <th>rho</th>\n",
       "      <th>last_trade_timestamp</th>\n",
       "      <th>dte</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPL240621C00005000</td>\n",
       "      <td>2024-06-21</td>\n",
       "      <td>5.0</td>\n",
       "      <td>call</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>209.2267</td>\n",
       "      <td>186.82</td>\n",
       "      <td>up</td>\n",
       "      <td>208.30</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0006</td>\n",
       "      <td>2024-05-30 15:17:02</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL240621P00005000</td>\n",
       "      <td>2024-06-21</td>\n",
       "      <td>5.0</td>\n",
       "      <td>put</td>\n",
       "      <td>35</td>\n",
       "      <td>6</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.01</td>\n",
       "      <td>no_change</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.005</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2024-06-18 13:34:12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAPL240621C00010000</td>\n",
       "      <td>2024-06-21</td>\n",
       "      <td>10.0</td>\n",
       "      <td>call</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>204.2299</td>\n",
       "      <td>171.61</td>\n",
       "      <td>down</td>\n",
       "      <td>203.40</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0010</td>\n",
       "      <td>2024-02-26 15:49:52</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL240621P00010000</td>\n",
       "      <td>2024-06-21</td>\n",
       "      <td>10.0</td>\n",
       "      <td>put</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.01</td>\n",
       "      <td>up</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2024-06-14 10:14:16</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPL240621C00015000</td>\n",
       "      <td>2024-06-21</td>\n",
       "      <td>15.0</td>\n",
       "      <td>call</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>199.2330</td>\n",
       "      <td>156.73</td>\n",
       "      <td>down</td>\n",
       "      <td>198.50</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0017</td>\n",
       "      <td>2024-03-28 09:31:59</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2121</th>\n",
       "      <td>AAPL261218P00330000</td>\n",
       "      <td>2026-12-18</td>\n",
       "      <td>330.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>115.7950</td>\n",
       "      <td>134.83</td>\n",
       "      <td>no_change</td>\n",
       "      <td>113.50</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.2884</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0285</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2024-06-06 15:39:43</td>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2122</th>\n",
       "      <td>AAPL261218C00340000</td>\n",
       "      <td>2026-12-18</td>\n",
       "      <td>340.0</td>\n",
       "      <td>call</td>\n",
       "      <td>174</td>\n",
       "      <td>21</td>\n",
       "      <td>8.2353</td>\n",
       "      <td>8.20</td>\n",
       "      <td>up</td>\n",
       "      <td>6.95</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.350</td>\n",
       "      <td>-0.0409</td>\n",
       "      <td>0.2340</td>\n",
       "      <td>0.2210</td>\n",
       "      <td>0.0038</td>\n",
       "      <td>-0.0113</td>\n",
       "      <td>1.0073</td>\n",
       "      <td>0.9640</td>\n",
       "      <td>2024-06-18 14:41:03</td>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2123</th>\n",
       "      <td>AAPL261218P00340000</td>\n",
       "      <td>2026-12-18</td>\n",
       "      <td>340.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>125.7950</td>\n",
       "      <td>126.45</td>\n",
       "      <td>up</td>\n",
       "      <td>123.55</td>\n",
       "      <td>...</td>\n",
       "      <td>3.775</td>\n",
       "      <td>0.0308</td>\n",
       "      <td>0.3039</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0285</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2024-06-18 11:18:51</td>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2124</th>\n",
       "      <td>AAPL261218C00350000</td>\n",
       "      <td>2026-12-18</td>\n",
       "      <td>350.0</td>\n",
       "      <td>call</td>\n",
       "      <td>2604</td>\n",
       "      <td>128</td>\n",
       "      <td>7.2132</td>\n",
       "      <td>7.40</td>\n",
       "      <td>no_change</td>\n",
       "      <td>7.10</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.025</td>\n",
       "      <td>-0.0034</td>\n",
       "      <td>0.2348</td>\n",
       "      <td>0.1989</td>\n",
       "      <td>0.0036</td>\n",
       "      <td>-0.0107</td>\n",
       "      <td>0.9493</td>\n",
       "      <td>0.8726</td>\n",
       "      <td>2024-06-18 15:33:39</td>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2125</th>\n",
       "      <td>AAPL261218P00350000</td>\n",
       "      <td>2026-12-18</td>\n",
       "      <td>350.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>135.7950</td>\n",
       "      <td>134.40</td>\n",
       "      <td>down</td>\n",
       "      <td>133.50</td>\n",
       "      <td>...</td>\n",
       "      <td>1.725</td>\n",
       "      <td>0.0130</td>\n",
       "      <td>0.3190</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0285</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2024-06-18 11:20:26</td>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2126 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          contract_symbol  expiration  strike option_type  open_interest  \\\n",
       "0     AAPL240621C00005000  2024-06-21     5.0        call             70   \n",
       "1     AAPL240621P00005000  2024-06-21     5.0         put             35   \n",
       "2     AAPL240621C00010000  2024-06-21    10.0        call              0   \n",
       "3     AAPL240621P00010000  2024-06-21    10.0         put              1   \n",
       "4     AAPL240621C00015000  2024-06-21    15.0        call              0   \n",
       "...                   ...         ...     ...         ...            ...   \n",
       "2121  AAPL261218P00330000  2026-12-18   330.0         put              0   \n",
       "2122  AAPL261218C00340000  2026-12-18   340.0        call            174   \n",
       "2123  AAPL261218P00340000  2026-12-18   340.0         put              0   \n",
       "2124  AAPL261218C00350000  2026-12-18   350.0        call           2604   \n",
       "2125  AAPL261218P00350000  2026-12-18   350.0         put              0   \n",
       "\n",
       "      volume  theoretical_price  last_trade_price       tick     bid  ...  \\\n",
       "0          0           209.2267            186.82         up  208.30  ...   \n",
       "1          6             0.0000              0.01  no_change    0.00  ...   \n",
       "2          0           204.2299            171.61       down  203.40  ...   \n",
       "3          0             0.0000              0.01         up    0.00  ...   \n",
       "4          0           199.2330            156.73       down  198.50  ...   \n",
       "...      ...                ...               ...        ...     ...  ...   \n",
       "2121       0           115.7950            134.83  no_change  113.50  ...   \n",
       "2122      21             8.2353              8.20         up    6.95  ...   \n",
       "2123      14           125.7950            126.45         up  123.55  ...   \n",
       "2124     128             7.2132              7.40  no_change    7.10  ...   \n",
       "2125       7           135.7950            134.40       down  133.50  ...   \n",
       "\n",
       "      change  change_percent  implied_volatility   delta   gamma   theta  \\\n",
       "0      0.000          0.0000              0.0000  1.0000  0.0000  0.0000   \n",
       "1      0.005          1.0000              0.0000  0.0000  0.0000  0.0000   \n",
       "2      0.000          0.0000              0.0000  1.0000  0.0000  0.0000   \n",
       "3      0.000          0.0000              0.0000  0.0000  0.0000  0.0000   \n",
       "4      0.000          0.0000              0.0000  1.0000  0.0000  0.0000   \n",
       "...      ...             ...                 ...     ...     ...     ...   \n",
       "2121   0.000          0.0000              0.2884 -1.0000  0.0000 -0.0285   \n",
       "2122  -0.350         -0.0409              0.2340  0.2210  0.0038 -0.0113   \n",
       "2123   3.775          0.0308              0.3039 -1.0000  0.0000 -0.0285   \n",
       "2124  -0.025         -0.0034              0.2348  0.1989  0.0036 -0.0107   \n",
       "2125   1.725          0.0130              0.3190 -1.0000  0.0000 -0.0285   \n",
       "\n",
       "        vega     rho  last_trade_timestamp  dte  \n",
       "0     0.0000  0.0006   2024-05-30 15:17:02    2  \n",
       "1     0.0000  0.0000   2024-06-18 13:34:12    2  \n",
       "2     0.0000  0.0010   2024-02-26 15:49:52    2  \n",
       "3     0.0000  0.0000   2024-06-14 10:14:16    2  \n",
       "4     0.0000  0.0017   2024-03-28 09:31:59    2  \n",
       "...      ...     ...                   ...  ...  \n",
       "2121  0.0000  0.0000   2024-06-06 15:39:43  912  \n",
       "2122  1.0073  0.9640   2024-06-18 14:41:03  912  \n",
       "2123  0.0000  0.0000   2024-06-18 11:18:51  912  \n",
       "2124  0.9493  0.8726   2024-06-18 15:33:39  912  \n",
       "2125  0.0000  0.0000   2024-06-18 11:20:26  912  \n",
       "\n",
       "[2126 rows x 27 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(chains)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78f3cc22",
   "metadata": {},
   "source": [
    "**Jason Strimpel** is the founder of <a href='https://pyquantnews.com/'>PyQuant News</a> and co-founder of <a href='https://www.tradeblotter.io/'>Trade Blotter</a>. His career in algorithmic trading spans 20+ years. He previously traded for a Chicago-based hedge fund, was a risk manager at JPMorgan, and managed production risk technology for an energy derivatives trading firm in London. In Singapore, he served as APAC CIO for an agricultural trading firm and built the data science team for a global metals trading firm. Jason holds degrees in Finance and Economics and a Master's in Quantitative Finance from the Illinois Institute of Technology. His career spans America, Europe, and Asia. He shares his expertise through the <a href='https://pyquantnews.com/subscribe-to-the-pyquant-newsletter/'>PyQuant Newsletter</a>, social media, and has taught over 1,000+ algorithmic trading with Python in his popular course **<a href='https://gettingstartedwithpythonforquantfinance.com/'>Getting Started With Python for Quant Finance</a>**. All code is for educational purposes only. Nothing provided here is financial advise. Use at your own risk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e90f42c6-ab8d-410a-b968-06d36877a815",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
